create or replace package  admassist.cust_hsd_date_util as
/*
    Copyright 2009 Highline Public Schools
--
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
--
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
--
    You should have received a copy of the GNU General Public License
    along with This program.  If not, see <http://www.gnu.org/licenses/>.
*/

--Y or N: do the date ranges overlap?
function date_overlaps
(
   start1 date,
   end1 date,
   start2 date,
   end2 date
)
return varchar2
deterministic;

--Y or N: did the date occur last summer?
function during_previous_summer
(
   p_school number,
   p_date_in_question date,
   p_schoolyear number
)
return varchar2;

--Given two date intervals, return the start or end of their unioned (ie shared) interval.
--p_return_what must be 'start' or 'end'
function interval_union
(
   p_start1 date,
   p_end1 date,
   p_start2 date,
   p_end2 date,
   p_return_what varchar2
)
return date
deterministic;

function person_age
(
   p_birthdate date,
   p_as_of date
)
return number
deterministic;

/*
Return the school day p_school_days after the p_start_date. 
If p_start_date is not a school day and p_school_days is zero, return the next school day.
*/
function school_days_add
(
   p_start_date date,
   p_school number,
   p_school_days number
)
return date;

end; --package
/
create or replace package body admassist.cust_hsd_date_util as
function date_overlaps
(
   start1 date,
   end1 date,
   start2 date,
   end2 date
)
return varchar2
deterministic
is
l_return varchar2(1);
begin
   if start1 <= end1
      and start2 <= end2
      and start1 <= end2 
      and start2 <= end1
         then l_return := 'Y';
   else l_return := 'N';
   end if;
   return l_return;
end;

function during_previous_summer
(
   p_school number,
   p_date_in_question date,
   p_schoolyear number
)
return varchar2
is
l_return varchar2(1);
begin
   select
      case 
         when p_date_in_question between
            (
            select lastyr.semester_end_date + to_dsinterval('1 00:00:00')
               from admassist.school_schedules lastyr
               where
                  lastyr.year = (p_schoolyear - 1)
                  and lastyr.school = p_school
                  and lastyr.semester = 0
            )
            and
            (
               select curr.semester_start_date - to_dsinterval('1 00:00:00')
               from admassist.school_schedules curr
               where
                  curr.year = p_schoolyear
                  and curr.school = p_school
                  and curr.semester = 0
            )
            then 'Y'
         else 'N'
      end s1
      into l_return
   from dual;
   return l_return;
end;

function interval_union
(
   p_start1 date,
   p_end1 date,
   p_start2 date,
   p_end2 date,
   p_return_what varchar2
)
return date
deterministic
is
l_union_start date;
l_union_end date;
l_return date;
begin
   l_return := null;
   l_union_start := greatest(p_start1, p_start2);
   l_union_end := least(p_end1, p_end2);
   if l_union_start <= l_union_end then
      l_return := 
         case p_return_what
            when 'start' then l_union_start
            when 'end' then l_union_end
         end;
   end if;
   return l_return;
end
;

function person_age
(
   p_birthdate date,
   p_as_of date
)
return number
deterministic
is
begin
   return FLOOR(MONTHS_BETWEEN(p_as_of,p_birthdate)/12);
end;

function school_days_add
(
   p_start_date date,
   p_school number,
   p_school_days number
)
return date is
   EPOCH constant date := to_date('1970-01-01','YYYY-MM-DD');
   l_return date := null;
   l_start_is_school_day varchar2(1);
   l_days_adjust number := 0;
begin
   case
      when p_school_days < 0 then raise_application_error(-20000,'Parameter p_school_days must be positive.');
      when p_start_date < EPOCH then raise_application_error(-20000,'Parameter p_start_date must be later than ' || to_char(EPOCH) || '.');
      when p_school_days = 0 then
         begin
            --is p_start_date a school day?
            select
               case
                  when to_char(p_start_date,'D') in ('1','7') then 'N'
                  when exists
                     (
                        select 1 from admassist.school_attendance_cycles c
                        where 1=1
                           and c.school = p_school
                           and c.effective_date = p_start_date
                     ) then 'Y'
                  else 'N'
               end into l_start_is_school_day
            from dual;
            if l_start_is_school_day = 'Y' then 
               l_return := p_start_date;
            else
               l_days_adjust := 1;
            end if;
         end;
      else l_return := null;
   end case;
   if l_return is null then
      select max(effective_date) into l_return from
      (
         select c.effective_date
         from admassist.school_attendance_cycles c
         where 1=1
            and c.school = p_school
            and c.school_year = aa$sis$get_school.SCHOOL_YEAR(p_school,p_start_date)
            and c.effective_date > p_start_date
            and rownum <= (p_school_days + l_days_adjust)
         order by c.effective_date
      )
      ;
   end if;
   return l_return;
exception
   when no_data_found then return null;
   when others then raise;
end;

end; --package body
/
